import pandas


class Basepandas(object):

    def __init__(self):
        self.df1 = None
        self.gr = None
        self.df4 = None

    def read_excel(self, filename, sheet_name):
        # 读取excel 文件名和sheet_name
        self.df1 = pandas.read_excel(filename, sheet_name)

    def group_by(self):
        # 分组 统计 并设置size名
        self.gr = self.df1.groupby(by=['刊名', '期数'], as_index=True).size().reset_index(name='Size')
        # data_many = self.gr[(self.gr ['Size'] > 1)]
        # print(data_many)

    def merge(self):
        # 合并左面为基础，合并的键
        self.df3 = pandas.merge(self.df1, self.gr, how='left', on=['刊名', '期数'])
        print(self.df3)

    def read_excel2(self, filename, sheet_name):
        self.df4 = pandas.read_excel(filename, sheet_name)
        # 获取指定列
        self.df5 = self.df4[["征订号", "推荐等级"]]
        # 更改列名字
        self.df5.columns = ["邮发", "推荐等级"]
        # 清除 缺失值
        self.df5 = self.df5.dropna()
        # 删除重复值
        self.df5 = self.df5.drop_duplicates()
        print(self.df5)
        # self.gr = self.df5.groupby(by=["邮发"], as_index=True).size().reset_index(name='Size')
        # data_many = self.gr[(self.gr['Size'] > 1)]
        # print(data_many)

    def merge2(self):
        self.df6 = pandas.merge(self.df3, self.df5, how='left', on=['邮发'])
        print(self.df6)

    def to_excel(self):
        writer = pandas.ExcelWriter('3028B 重庆维普 2019年已发明细_result.xls')
        sheet_name = '数据表'
        self.df6.to_excel(writer, sheet_name)
        writer.save()


bp = Basepandas()
bp.read_excel(r"F:\excel\master\3028B 重庆维普 2019年已发明细.xls", 0)
bp.group_by()
bp.merge()
bp.read_excel2(r"F:\excel\master\人天订购期刊列表-维普2019订单.xls", "订单主表")
bp.merge2()
bp.to_excel()